/*
 * ------------------------------------------------------------------
 * Copyright © 2018 Hangzhou DtDream Technology Co.,Lt d. All rights reserved.
 * ------------------------------------------------------------------
 *       Product:
 *   Module Name:
 *  Date Created: 2021-03-17
 *   Description:
 * ------------------------------------------------------------------
 * Modification History
 * DATE            Name           Description
 * ------------------------------------------------------------------
 * 2021-03-17     x0334
 * ------------------------------------------------------------------
 */

package com.example.demo001;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang3.StringUtils;

public class TestPattern {
    public static List<String> SQL_TEST = new ArrayList<>();

    public static void initSQLTest(){
        SQL_TEST.add(0,"select * from lonple_003");
        SQL_TEST.add(1,"select * from lonple_001 union all select * from lonple_002");
        SQL_TEST.add(2,"select * from lonple_003 order by name");
        SQL_TEST.add(3,"select l.name, ll.country, ll.name from lonple_001 l left join lonple_002 ll on l.name = ll" +
                ".name");
        SQL_TEST.add(4,"select collect_list(name) from lonple_002 limit 1000");
        SQL_TEST.add(5,"select l.name, country from lonple_002 l left join lonple_003 ll on l.name = ll.name");
        SQL_TEST.add(6,"select name , name as username, age as userAge from lonple_002;");
        SQL_TEST.add(7,"select * from (select * from lonple_002) lonple_002;");
        SQL_TEST.add(8,"select * from lonple_001 l,lonple_002 ll where l.name = ll.name;");
        SQL_TEST.add(9,"select l.name, ll.age, ll.birthday from lonple_002 l left join lonple_001 ll on "
                + "l.name = ll.name;");
        SQL_TEST.add(10,"select name, age, birthday from lonple_002 l " +
                "union select  name, age, getdate() from lonple_003 union all select name , age, birthday from " +
                "lonple_001;");
        SQL_TEST.add(11,"select name, count(*) as aa from lonple_002 where name = 'lonple' group by name order by " +
                "name limit 10");

        SQL_TEST.add(12,"select name, count(*) as aa from (select * from lonple_002) lonple_002 group by name");
        SQL_TEST.add(13,"select \"name\", count(*) as aa from (select * from lonple_002) lonple_002 group by name");
    }

    public static void main(String[] args) {
        initSQLTest();
        String sql = SQL_TEST.get(13);
        sql = refactorSql(sql);
        System.out.println("sql: " + sql);


        String aa = "aabbcc";
        aa = aa.replace("bb", "aa");
        System.out.println("aa: " + aa);
    }


    private static String refactorSql(String sql){
        if(StringUtils.isBlank(sql)){
            return sql;
        }
        String rule = "(select(.*?)from)";
        Pattern compile = Pattern.compile(rule);
        Matcher matcher = compile.matcher(sql);
        while(matcher.find()){
            String sqlSelect = matcher.group(0);
            System.out.println("[sqlSelect 1]: " + sqlSelect);
            String sqlSelectReplace = replaceField(sqlSelect);
            sql = sql.replace(sqlSelect, sqlSelectReplace);
        }
        return sql;
    }

    private static String replaceField(String sqlSelect){
        String fieldRule = "(,)?(\\s)*\".*?\"(\\s)*(,)?";
        Pattern compileField = Pattern.compile(fieldRule);
        Matcher matcherField = compileField.matcher(sqlSelect);
        while(matcherField.find()){
            String sqlField = matcherField.group(0);
            System.out.println("[replaceField 2]: " + sqlField);
            String sqlFieldReplace = sqlField.replaceFirst("\"", "'");
            int lastQuoteIndex = sqlFieldReplace.lastIndexOf("\"");
            sqlFieldReplace = replaceCharByIndex(sqlFieldReplace, lastQuoteIndex,'\'');
            sqlSelect = sqlSelect.replace(sqlField, sqlFieldReplace);
        }
        return sqlSelect;
    }

    private static String replaceCharByIndex(String text, int index, char replaceChar){
        if(StringUtils.isEmpty(text)){
            return text;
        }
        char[] chars = text.toCharArray();
        if(index >= chars.length){
            return text;
        }
        chars[index] = replaceChar;
        return new String(chars);
    }
}
